create table code_master(
 code_no number primary key,
 code_type varchar2(50) not null,
 code_code varchar2(50) unique not null,
 code_name varchar2(200) not null
);
drop table code_master;

create sequence code_seq;
drop sequence code_seq;

SELECT * FROM CODE_MASTER;

insert into code_master(code_no,code_type,code_code,code_name) values(code_seq.nextval,'0000','A000','성별');
insert into code_master(code_no,code_type,code_code,code_name) values(code_seq.nextval,'0000','B000','회원등급');
insert into code_master(code_no,code_type,code_code,code_name) values(code_seq.nextval,'0000','C000','과목');
insert into code_master(code_no,code_type,code_code,code_name) values(code_seq.nextval,'0000','D000','학년');
insert into code_master(code_no,code_type,code_code,code_name) values(code_seq.nextval,'0000','E000','학력');
insert into code_master(code_no,code_type,code_code,code_name) values(code_seq.nextval,'0000','F000','과외희망지역');

insert into code_master(code_no,code_type,code_code,code_name) values(code_seq.nextval,'A000','A001','남자');
insert into code_master(code_no,code_type,code_code,code_name) values(code_seq.nextval,'A000','A002','여자');
insert into code_master(code_no,code_type,code_code,code_name) values(code_seq.nextval,'A000','A003','무관');

insert into code_master(code_no,code_type,code_code,code_name) values(code_seq.nextval,'B000','B001','학생');
insert into code_master(code_no,code_type,code_code,code_name) values(code_seq.nextval,'B000','B002','선생님');
insert into code_master(code_no,code_type,code_code,code_name) values(code_seq.nextval,'B000','B003','무관');

insert into code_master(code_no,code_type,code_code,code_name) values(code_seq.nextval,'C000','C101','국어');
insert into code_master(code_no,code_type,code_code,code_name) values(code_seq.nextval,'C000','C201','인문계 수학');
insert into code_master(code_no,code_type,code_code,code_name) values(code_seq.nextval,'C000','C202','자연계 수학');
insert into code_master(code_no,code_type,code_code,code_name) values(code_seq.nextval,'C000','C301','영어');
insert into code_master(code_no,code_type,code_code,code_name) values(code_seq.nextval,'C000','C401','물리');
insert into code_master(code_no,code_type,code_code,code_name) values(code_seq.nextval,'C000','C402','화학');
insert into code_master(code_no,code_type,code_code,code_name) values(code_seq.nextval,'C000','C403','생물');
insert into code_master(code_no,code_type,code_code,code_name) values(code_seq.nextval,'C000','C501','한국사');
insert into code_master(code_no,code_type,code_code,code_name) values(code_seq.nextval,'C000','C502','한국지리');
insert into code_master(code_no,code_type,code_code,code_name) values(code_seq.nextval,'C000','C503','경제');

insert into code_master(code_no,code_type,code_code,code_name) values(code_seq.nextval,'D000','D101','초등학교 1학년');
insert into code_master(code_no,code_type,code_code,code_name) values(code_seq.nextval,'D000','D102','초등학교 2학년');
insert into code_master(code_no,code_type,code_code,code_name) values(code_seq.nextval,'D000','D103','초등학교 3학년');
insert into code_master(code_no,code_type,code_code,code_name) values(code_seq.nextval,'D000','D104','초등학교 4학년');
insert into code_master(code_no,code_type,code_code,code_name) values(code_seq.nextval,'D000','D105','초등학교 5학년');
insert into code_master(code_no,code_type,code_code,code_name) values(code_seq.nextval,'D000','D106','초등학교 6학년');
insert into code_master(code_no,code_type,code_code,code_name) values(code_seq.nextval,'D000','D107','초등학교 무관');
insert into code_master(code_no,code_type,code_code,code_name) values(code_seq.nextval,'D000','D201','중학교 1학년');
insert into code_master(code_no,code_type,code_code,code_name) values(code_seq.nextval,'D000','D202','중학교 2학년');
insert into code_master(code_no,code_type,code_code,code_name) values(code_seq.nextval,'D000','D203','중학교 3학년');
insert into code_master(code_no,code_type,code_code,code_name) values(code_seq.nextval,'D000','D204','중학교 무관');
insert into code_master(code_no,code_type,code_code,code_name) values(code_seq.nextval,'D000','D301','고등학교 1학년');
insert into code_master(code_no,code_type,code_code,code_name) values(code_seq.nextval,'D000','D302','고등학교 2학년');
insert into code_master(code_no,code_type,code_code,code_name) values(code_seq.nextval,'D000','D303','고등학교 3학년');
insert into code_master(code_no,code_type,code_code,code_name) values(code_seq.nextval,'D000','D304','고등학교 무관');
insert into code_master(code_no,code_type,code_code,code_name) values(code_seq.nextval,'D000','D401','재수생');
insert into code_master(code_no,code_type,code_code,code_name) values(code_seq.nextval,'D000','D402','일반인');
insert into code_master(code_no,code_type,code_code,code_name) values(code_seq.nextval,'D000','D501','전체 무관');

insert into code_master(code_no,code_type,code_code,code_name) values(code_seq.nextval,'E000','E101','대학교 1학년');
insert into code_master(code_no,code_type,code_code,code_name) values(code_seq.nextval,'E000','E102','대학교 2학년');
insert into code_master(code_no,code_type,code_code,code_name) values(code_seq.nextval,'E000','E103','대학교 3학년');
insert into code_master(code_no,code_type,code_code,code_name) values(code_seq.nextval,'E000','E104','대학교 4학년');
insert into code_master(code_no,code_type,code_code,code_name) values(code_seq.nextval,'E000','E201','학사');
insert into code_master(code_no,code_type,code_code,code_name) values(code_seq.nextval,'E000','E202','석사');
insert into code_master(code_no,code_type,code_code,code_name) values(code_seq.nextval,'E000','E203','박사');
insert into code_master(code_no,code_type,code_code,code_name) values(code_seq.nextval,'E000','E204','전문선생님');


create table notice(
 no number primary key,
 title varchar2(200) not null,
 dateposted date not null,
 writer varchar2(50) not null,
 hits number default 0,
 orgfilename varchar2(200),
 savfilename varchar2(200),
 content varchar2(3000) not null
);
drop table notice;

create sequence notice_seq;
drop sequence notice_seq;

insert into notice(no,title,dateposted,writer,content) values(notice_seq.nextval,'테스트',sysdate,'관리자','테스트용 입니다.');

select * from notice;

create table help(
 no number primary key,
 id varchar2(50) not null,
 title varchar2(200) not null,
 dateposted date not null,
 hits number default 0,
 content varchar2(3000) not null,
 ref number default 0,
 restep number default 0,
 relevel number default 0
);
drop table help;

create sequence help_seq;
drop sequence help_seq;

insert into help(no,id,title,content,dateposted) 
values(help_seq.nextval,'admin','테스트','테스트입니다.',sysdate);

select * from help;

select * from addresscode where dong='나운2동';
--선생님 테이블

drop table wmt_teacher;

create table wmt_teacher(
 	teacherid varchar2(50) primary key, 
	name varchar2(50) not null,
	password varchar2(50) not null,
	gender varchar2(50) not null,--code
	tel varchar2(50) not null,
	hoperegion varchar2(50) not null,--code
	email varchar2(50) not null,
	questionPass varchar2(100) not null,
	answerpass varchar2(100) not null, 
	originalfilename varchar2(100),
	savefilename varchar2(100),
	enrolldate varchar2(50) not null,
	withdrawaldate varchar2(50)not null,
	university varchar2(50) not null, 
	hopesubject varchar2(50) not null, --code
	hopegender varchar2(50) not null, --code
	availablestudent varchar2(50) not null, --code
	availableday varchar2(50) not null, -- 선택된 요일을 묶어서 저장 "월수"
	availabletime varchar2(50) not null, -- 가능 시작 시간과 종료시간을 묶어서 저장 "0305"
	intro varchar2(1000) not null,
	CONSTRAINT fk_teacher_gender
	foreign key(gender) references code_master(code_code),
	CONSTRAINT fk_teacher_hoperegion
	foreign key(hoperegion) references code_master(code_code),
	CONSTRAINT fk_teacher_hopesubject
	foreign key(hopesubject) references code_master(code_code),
	CONSTRAINT fk_teacher_hopegender
	foreign key(hopegender) references code_master(code_code),
	CONSTRAINT fk_teacher_availablestudent
	foreign key(availablestudent) references code_master(code_code)
 )

 alter table wmt_teacher modify withdrawaldate varchar2(50) null
 select * from wmt_teacher
 update wmt_teacher set withdrawaldate=null where id ='java'

 ALTER TABLE wmt_student DROP COLUMN GRADE

 --학생 테이블
  create table wmt_student(
   studentid varchar2(50) primary key,
   name varchar2(50) not null,
   password varchar2(50) not null,
   gender varchar2(50) not null, --code
   tel varchar2(50) not null,
   hoperegion varchar2(50) not null, --code
   email varchar2(50) not null,
   questionpass varchar2(100) not null,
   answerpass varchar2(100) not null,
   enrolldate varchar2(50) not null,
   withdrawaldate varchar2(50),
   hopesubject varchar2(50) not null,--code ,사이즈 변경
   zipcode varchar2(10) not null,
   codeaddress varchar2(200) not null, 
   subaddress varchar2(150) not null,
   CONSTRAINT fk_student_gender
	foreign key(gender) references code_master(code_code),
	CONSTRAINT fk_student_hoperegion
	foreign key(hoperegion) references code_master(code_code),
	CONSTRAINT fk_student_hopesubject
	foreign key(hopesubject) references code_master(code_code)
   
  )
   alter table wmt_student modify withdrawaldate varchar2(50) null
   alter table wmt_student modify hopesubject varchar2(50) not null

  --선생님,학생 리스트 인서트 sql
   insert into WMT_teacher values('java','아이유','1111','여','1234','서울','iu@naver.com','할아버지 성함?','아이유할배',
  'iu','new iu','0619','0620','중앙대','수학','여','중3','월','3시','안냥');
  insert into WMT_teacher values('java3','아이유','1111','여','1234','서울','iu@naver.com','할아버지 성함?','아이유할배',
  'iu','new iu','0619','0620','중앙대','수학','여','중3','월','3시','안냥');
  
  insert into WMT_teacher(teacherid,name,password,gender,
  			tel,hoperegion,email,questionpass,answerpass,
 			enrolldate,university,hopesubject,
 			hopegender,availablestudent,availableday,availabletime,intro) 
 			values('teacher1','수지','1111','A002',
 			'1234','FC19','suzi@naver.com','할아버지 성함?','아이유할배',
  			'0619','중앙대','C202',
  			'A002','D203','월수','0305','안냥');
  insert into WMT_teacher(teacherid,name,password,gender,
  			tel,hoperegion,email,questionpass,answerpass,
 			enrolldate,university,hopesubject,
 			hopegender,availablestudent,availableday,availabletime,intro) 
 			values('teacher2','수현','1111','A001',
 			'1234','FC20','suhyun@naver.com','할아버지 성함?','수현할배',
  			'0619','중앙대','C301',
  			'A002','D202','수','1205','안냥');
  insert into WMT_teacher(teacherid,name,password,gender,
  			tel,hoperegion,email,questionpass,answerpass,
 			enrolldate,university,hopesubject,
 			hopegender,availablestudent,availableday,availabletime,intro) 
 			values('teacher3','현우','1111','A001',
 			'1234','FC21','hyunwoo@naver.com','할아버지 성함?','현우할배',
  			'0619','중앙대','C101',
  			'A002','D201','수','0812','안냥');
  			
 insert into WMT_student(studentid,name,password,
   gender, tel,
   hoperegion,email,questionpass,answerpass,enrolldate,
   hopesubject,zipcode,codeaddress,subaddress)
 			values('student1','아이유','1111',
 			'A002','1234',
 			'FC19','iu@naver.com','할아버지 성함?','아이유할배','0619',
 			'C202','20','성남시 분당구', '703동 502호');
 insert into WMT_student(studentid,name,password,
   gender, tel,
   hoperegion,email,questionpass,answerpass,enrolldate,
   hopesubject,zipcode,codeaddress,subaddress)
 			values('student2','연희','1111',
 			'A002','1234',
 			'FC20','yeonhee@naver.com','할아버지 성함?','연희할배','0619',
 			'C301','21','성남시 수정구', '703동 503호');
 insert into WMT_student(studentid,name,password,
   gender, tel,
   hoperegion,email,questionpass,answerpass,enrolldate,
   hopesubject,zipcode,codeaddress,subaddress)
 			values('student3','승기','1111',
 			'A001','1234',
 			'FC21','suzi@naver.com','할아버지 성함?','승기할배','0619',
 			'C101','22','성남시 중원구', '703동 504호');

  
  
 drop table wmt_teacher_lessonlist
  --선생님이 찜한 리스트
 create table wmt_teacher_lessonlist(
	no number primary key,
	teacherid varchar2(50) not null,
	lessonno number not null,
	CONSTRAINT fk_teacherlesson_teacher 
	foreign key(teacherid) references wmt_teacher(teacherid),
	CONSTRAINT fk_teacherlesson_lesson 
	foreign key(lessonno) references wmt_lesson(lessonno)
)
--학생이 선생이 찜한 리스트
create table wmt_student_teacherlist(
	no number primary key,
	studentid varchar2(50) not null,
	teacherid varchar2(50) not null,
	CONSTRAINT fk_studentteacher_student 
	foreign key(studentid) references wmt_student(studentid),
	CONSTRAINT fk_studentteacher_teacher 
	foreign key(teacherid) references wmt_teacher(teacherid)
)
drop table wmt_student_teacherlist
--수업등록 테이블
insert into wmt_lesson values(lessonno_seq.nextval,'수업등록 테스트','수업등록 글쓰기 테스트',
'수학','0',sysdate,'3시','연결가능','java','brooklerin')
alter table test add(osy number(10));
alter table wmt_lesson modify(classteacherid varchar2(100) null);
select lessonno_seq.nextval from dual
CREATE TABLE wmt_lesson(
	lessonno number primary key,
	studentid varchar2(50) not null,
	title varchar2(100) not null,
	content varchar2(2000),
	hits number default 0,
	dateposted date not null,
	hopeday varchar2(100) not null,
	hopetime varchar2(100) not null,
	connectstate varchar2(50) default '연결가능',
	classteacherid varchar2(50) not null,
	CONSTRAINT FK_lesson_student 
	foreign key(studentid) 
	references wmt_student(studentid),
	CONSTRAINT FK_lesson_classteacherid 
	foreign key(classteacherid) 
	references wmt_teacher(teacherid)
)
create sequence lessonno_seq;
drop table wmt_lesson
select *  from wmt_lesson
<<<<<<< .mine

alter table wmt_teacher add (uploadfile varchar(50) null);
 
insert into code_master(code_no,code_type,code_code,code_name) values(code_seq.nextval,'0000','F000','과외희망지역');

insert into code_master values(code_seq.nextval,'F000','FA00','서울특별시');--시군구선택
insert into code_master values(code_seq.nextval,'F000','FB00','인천광역시');
insert into code_master values(code_seq.nextval,'F000','FC00','경기도');
insert into code_master values(code_seq.nextval,'F000','FD00','부산광역시');
insert into code_master values(code_seq.nextval,'F000','FE00','대구광역시');
insert into code_master values(code_seq.nextval,'F000','FF00','광주광역시');
insert into code_master values(code_seq.nextval,'F000','FG00','대전광역시');
insert into code_master values(code_seq.nextval,'F000','FH00','울산광역시');
insert into code_master values(code_seq.nextval,'F000','FI00','강원도');
insert into code_master values(code_seq.nextval,'F000','FJ00','경상남도');
insert into code_master values(code_seq.nextval,'F000','FK00','경상북도');
insert into code_master values(code_seq.nextval,'F000','FL00','전라남도');
insert into code_master values(code_seq.nextval,'F000','FM00','전라북도');
insert into code_master values(code_seq.nextval,'F000','FN00','제주도');
insert into code_master values(code_seq.nextval,'F000','FO00','충청남도');
insert into code_master values(code_seq.nextval,'F000','FP00','충청북도');



insert into code_master values(code_seq.nextval,'FA00','FA01','강남구');	--서울특별시
insert into code_master values(code_seq.nextval,'FA00','FA02','강동구');
insert into code_master values(code_seq.nextval,'FA00','FA03','강북구');
insert into code_master values(code_seq.nextval,'FA00','FA04','강서구');
insert into code_master values(code_seq.nextval,'FA00','FA05','관악구');
insert into code_master values(code_seq.nextval,'FA00','FA06','광진구');
insert into code_master values(code_seq.nextval,'FA00','FA07','구로구');
insert into code_master values(code_seq.nextval,'FA00','FA08','금천구');
insert into code_master values(code_seq.nextval,'FA00','FA09','노원구');
insert into code_master values(code_seq.nextval,'FA00','FA10','도봉구');
insert into code_master values(code_seq.nextval,'FA00','FA11','동대문구');
insert into code_master values(code_seq.nextval,'FA00','FA12','동작구');
insert into code_master values(code_seq.nextval,'FA00','FA13','마포구');
insert into code_master values(code_seq.nextval,'FA00','FA14','서대문구');
insert into code_master values(code_seq.nextval,'FA00','FA15','서초구');
insert into code_master values(code_seq.nextval,'FA00','FA16','성동구');
insert into code_master values(code_seq.nextval,'FA00','FA17','성북구');
insert into code_master values(code_seq.nextval,'FA00','FA18','송파구');
insert into code_master values(code_seq.nextval,'FA00','FA19','양천구');
insert into code_master values(code_seq.nextval,'FA00','FA20','영등포구');
insert into code_master values(code_seq.nextval,'FA00','FA21','용산구');
insert into code_master values(code_seq.nextval,'FA00','FA22','은평구');
insert into code_master values(code_seq.nextval,'FA00','FA23','종로구');
insert into code_master values(code_seq.nextval,'FA00','FA24','중구');
insert into code_master values(code_seq.nextval,'FA00','FA25','중랑구');

insert into code_master values(code_seq.nextval,'FB00','FB01','강화군');	--인천광역시
insert into code_master values(code_seq.nextval,'FB00','FB02','계양구');
insert into code_master values(code_seq.nextval,'FB00','FB03','남구');
insert into code_master values(code_seq.nextval,'FB00','FB04','남동구');
insert into code_master values(code_seq.nextval,'FB00','FB05','동구');
insert into code_master values(code_seq.nextval,'FB00','FB06','부평구');
insert into code_master values(code_seq.nextval,'FB00','FB07','서구');
insert into code_master values(code_seq.nextval,'FB00','FB08','연수구');
insert into code_master values(code_seq.nextval,'FB00','FB09','웅진군');
insert into code_master values(code_seq.nextval,'FB00','FB10','중구');

insert into code_master values(code_seq.nextval,'FC00','FC01','가평군');	--경기도
insert into code_master values(code_seq.nextval,'FC00','FC02','고양시');
insert into code_master values(code_seq.nextval,'FC00','FC03','고양 덕양구');
insert into code_master values(code_seq.nextval,'FC00','FC04','고양 일산동구');
insert into code_master values(code_seq.nextval,'FC00','FC05','고양 일산서구');
insert into code_master values(code_seq.nextval,'FC00','FC06','과천');
insert into code_master values(code_seq.nextval,'FC00','FC07','광명');
insert into code_master values(code_seq.nextval,'FC00','FC08','광주');
insert into code_master values(code_seq.nextval,'FC00','FC09','구리');
insert into code_master values(code_seq.nextval,'FC00','FC10','군포');
insert into code_master values(code_seq.nextval,'FC00','FC11','김포');
insert into code_master values(code_seq.nextval,'FC00','FC12','남양주');
insert into code_master values(code_seq.nextval,'FC00','FC13','동두천');
insert into code_master values(code_seq.nextval,'FC00','FC14','부천');
insert into code_master values(code_seq.nextval,'FC00','FC15','부천 소사구');
insert into code_master values(code_seq.nextval,'FC00','FC16','부천 오정구');
insert into code_master values(code_seq.nextval,'FC00','FC17','부천 원미구');
insert into code_master values(code_seq.nextval,'FC00','FC18','성남');
insert into code_master values(code_seq.nextval,'FC00','FC19','성남 분당구');
insert into code_master values(code_seq.nextval,'FC00','FC20','성남 수정구');
insert into code_master values(code_seq.nextval,'FC00','FC21','성남 중원구');
insert into code_master values(code_seq.nextval,'FC00','FC22','수원');
insert into code_master values(code_seq.nextval,'FC00','FC23','수원 권선구');
insert into code_master values(code_seq.nextval,'FC00','FC24','수원 영통구');
insert into code_master values(code_seq.nextval,'FC00','FC25','수원 장안구');
insert into code_master values(code_seq.nextval,'FC00','FC26','수원 팔달구');
insert into code_master values(code_seq.nextval,'FC00','FC27','시흥');
insert into code_master values(code_seq.nextval,'FC00','FC28','안산');
insert into code_master values(code_seq.nextval,'FC00','FC29','안산 단원구');
insert into code_master values(code_seq.nextval,'FC00','FC30','안산 상록구');
insert into code_master values(code_seq.nextval,'FC00','FC31','안성');
insert into code_master values(code_seq.nextval,'FC00','FC32','안양');
insert into code_master values(code_seq.nextval,'FC00','FC33','안양 동안구');
insert into code_master values(code_seq.nextval,'FC00','FC34','안양 만안구');
insert into code_master values(code_seq.nextval,'FC00','FC35','양주');
insert into code_master values(code_seq.nextval,'FC00','FC36','양평군');
insert into code_master values(code_seq.nextval,'FC00','FC37','여주군');
insert into code_master values(code_seq.nextval,'FC00','FC38','연천군');
insert into code_master values(code_seq.nextval,'FC00','FC39','오산');
insert into code_master values(code_seq.nextval,'FC00','FC40','용인');
insert into code_master values(code_seq.nextval,'FC00','FC41','용인 기흥구');
insert into code_master values(code_seq.nextval,'FC00','FC42','용인 수지구');
insert into code_master values(code_seq.nextval,'FC00','FC43','용인 처인구');
insert into code_master values(code_seq.nextval,'FC00','FC44','의왕');
insert into code_master values(code_seq.nextval,'FC00','FC45','의정부');
insert into code_master values(code_seq.nextval,'FC00','FC46','이천');
insert into code_master values(code_seq.nextval,'FC00','FC47','파주');
insert into code_master values(code_seq.nextval,'FC00','FC48','평택');
insert into code_master values(code_seq.nextval,'FC00','FC49','포천');
insert into code_master values(code_seq.nextval,'FC00','FC50','하남');
insert into code_master values(code_seq.nextval,'FC00','FC51','화성');

insert into code_master values(code_seq.nextval,'FD00','FD01','강서구');--부산광역시
insert into code_master values(code_seq.nextval,'FD00','FD02','금정구');
insert into code_master values(code_seq.nextval,'FD00','FD03','남구');
insert into code_master values(code_seq.nextval,'FD00','FD04','동구');
insert into code_master values(code_seq.nextval,'FD00','FD05','동래구');
insert into code_master values(code_seq.nextval,'FD00','FD06','부산진구');
insert into code_master values(code_seq.nextval,'FD00','FD07','북구');
insert into code_master values(code_seq.nextval,'FD00','FD08','사상구');
insert into code_master values(code_seq.nextval,'FD00','FD09','사하구');
insert into code_master values(code_seq.nextval,'FD00','FD10','서구');
insert into code_master values(code_seq.nextval,'FD00','FD11','수영구');
insert into code_master values(code_seq.nextval,'FD00','FD12','연제구');
insert into code_master values(code_seq.nextval,'FD00','FD13','영도구');
insert into code_master values(code_seq.nextval,'FD00','FD14','중구구');
insert into code_master values(code_seq.nextval,'FD00','FD15','해운대구');
insert into code_master values(code_seq.nextval,'FD00','FD16','기장군');

insert into code_master values(code_seq.nextval,'FE00','FE01','남구');--대구광역시
insert into code_master values(code_seq.nextval,'FE00','FE02','달서구');
insert into code_master values(code_seq.nextval,'FE00','FE03','동구');
insert into code_master values(code_seq.nextval,'FE00','FE04','북구');
insert into code_master values(code_seq.nextval,'FE00','FE05','서구');
insert into code_master values(code_seq.nextval,'FE00','FE06','수성구');
insert into code_master values(code_seq.nextval,'FE00','FE07','중구');
insert into code_master values(code_seq.nextval,'FE00','FE08','달성군');

insert into code_master values(code_seq.nextval,'FF00','FF01','광산구');--광주광역시
insert into code_master values(code_seq.nextval,'FF00','FF02','남구');
insert into code_master values(code_seq.nextval,'FF00','FF03','동구');
insert into code_master values(code_seq.nextval,'FF00','FF04','서구');


insert into code_master values(code_seq.nextval,'FG00','FG01','대덕구');--대전광역시
insert into code_master values(code_seq.nextval,'FG00','FG02','동구');
insert into code_master values(code_seq.nextval,'FG00','FG03','서구');
insert into code_master values(code_seq.nextval,'FG00','FG04','유성구');
insert into code_master values(code_seq.nextval,'FG00','FG05','중구');

insert into code_master values(code_seq.nextval,'FH00','FH01','남구');--울산광역시
insert into code_master values(code_seq.nextval,'FH00','FH02','동구');
insert into code_master values(code_seq.nextval,'FH00','FH03','북구');
insert into code_master values(code_seq.nextval,'FH00','FH04','중구');
insert into code_master values(code_seq.nextval,'FH00','FH05','울주군');

insert into code_master values(code_seq.nextval,'FI00','FI01','강릉시');--강원도
insert into code_master values(code_seq.nextval,'FI00','FI02','동해시');
insert into code_master values(code_seq.nextval,'FI00','FI03','삼척시');
insert into code_master values(code_seq.nextval,'FI00','FI04','속초시');
insert into code_master values(code_seq.nextval,'FI00','FI05','원주시');
insert into code_master values(code_seq.nextval,'FI00','FI06','춘천시');
insert into code_master values(code_seq.nextval,'FI00','FI07','태백시');
insert into code_master values(code_seq.nextval,'FI00','FI08','고성군');
insert into code_master values(code_seq.nextval,'FI00','FI09','양구군');
insert into code_master values(code_seq.nextval,'FI00','FI10','양양군');
insert into code_master values(code_seq.nextval,'FI00','FI11','영월군');
insert into code_master values(code_seq.nextval,'FI00','FI12','인제군');
insert into code_master values(code_seq.nextval,'FI00','FI13','정선군');
insert into code_master values(code_seq.nextval,'FI00','FI14','철원군');
insert into code_master values(code_seq.nextval,'FI00','FI15','평창군');
insert into code_master values(code_seq.nextval,'FI00','FI16','홍천군');
insert into code_master values(code_seq.nextval,'FI00','FI17','화천군');
insert into code_master values(code_seq.nextval,'FI00','FI18','횡성군');

insert into code_master values(code_seq.nextval,'FJ00','FJ01','거제시');--경상남도
insert into code_master values(code_seq.nextval,'FJ00','FJ02','김해시');
insert into code_master values(code_seq.nextval,'FJ00','FJ03','마산시');
insert into code_master values(code_seq.nextval,'FJ00','FJ04','밀양시');
insert into code_master values(code_seq.nextval,'FJ00','FJ05','사천시');
insert into code_master values(code_seq.nextval,'FJ00','FJ06','양산시');
insert into code_master values(code_seq.nextval,'FJ00','FJ07','진주시');
insert into code_master values(code_seq.nextval,'FJ00','FJ08','진해시');
insert into code_master values(code_seq.nextval,'FJ00','FJ09','창원시');
insert into code_master values(code_seq.nextval,'FJ00','FJ10','통영시');
insert into code_master values(code_seq.nextval,'FJ00','FJ11','거창군');
insert into code_master values(code_seq.nextval,'FJ00','FJ12','고성군');
insert into code_master values(code_seq.nextval,'FJ00','FJ13','남해군');
insert into code_master values(code_seq.nextval,'FJ00','FJ14','산청군');
insert into code_master values(code_seq.nextval,'FJ00','FJ15','의령군');
insert into code_master values(code_seq.nextval,'FJ00','FJ16','창녕군');
insert into code_master values(code_seq.nextval,'FJ00','FJ17','하동군');
insert into code_master values(code_seq.nextval,'FJ00','FJ18','함안군');
insert into code_master values(code_seq.nextval,'FJ00','FJ19','함양군');
insert into code_master values(code_seq.nextval,'FJ00','FJ20','합천군');

insert into code_master values(code_seq.nextval,'FK00','FK01','경산시');--경상북도
insert into code_master values(code_seq.nextval,'FK00','FK02','경주시');
insert into code_master values(code_seq.nextval,'FK00','FK03','구미시');
insert into code_master values(code_seq.nextval,'FK00','FK04','김천시');
insert into code_master values(code_seq.nextval,'FK00','FK05','문경시');
insert into code_master values(code_seq.nextval,'FK00','FK06','상주시');
insert into code_master values(code_seq.nextval,'FK00','FK07','안동시');
insert into code_master values(code_seq.nextval,'FK00','FK08','영주시');
insert into code_master values(code_seq.nextval,'FK00','FK09','영천시');
insert into code_master values(code_seq.nextval,'FK00','FK10','포항시 남구');
insert into code_master values(code_seq.nextval,'FK00','FK11','포항시 북구');
insert into code_master values(code_seq.nextval,'FK00','FK12','고령군');
insert into code_master values(code_seq.nextval,'FK00','FK13','군위군');
insert into code_master values(code_seq.nextval,'FK00','FK14','봉화군');
insert into code_master values(code_seq.nextval,'FK00','FK15','성주군');
insert into code_master values(code_seq.nextval,'FK00','FK16','영덕군');
insert into code_master values(code_seq.nextval,'FK00','FK17','영양군');
insert into code_master values(code_seq.nextval,'FK00','FK18','예천군');
insert into code_master values(code_seq.nextval,'FK00','FK19','울릉군');
insert into code_master values(code_seq.nextval,'FK00','FK20','울진군');
insert into code_master values(code_seq.nextval,'FK00','FK21','의성군');
insert into code_master values(code_seq.nextval,'FK00','FK22','청도군');
insert into code_master values(code_seq.nextval,'FK00','FK23','청송군');
insert into code_master values(code_seq.nextval,'FK00','FK24','칠곡군');

insert into code_master values(code_seq.nextval,'FL00','FL01','광양시');--전라남도
insert into code_master values(code_seq.nextval,'FL00','FL02','나주시');
insert into code_master values(code_seq.nextval,'FL00','FL03','목포시');
insert into code_master values(code_seq.nextval,'FL00','FL04','순천시');
insert into code_master values(code_seq.nextval,'FL00','FL05','여수시');
insert into code_master values(code_seq.nextval,'FL00','FL06','강진군');
insert into code_master values(code_seq.nextval,'FL00','FL07','고흥군');
insert into code_master values(code_seq.nextval,'FL00','FL08','곡성군');
insert into code_master values(code_seq.nextval,'FL00','FL09','구례군');
insert into code_master values(code_seq.nextval,'FL00','FL10','담양군');
insert into code_master values(code_seq.nextval,'FL00','FL11','무안군');
insert into code_master values(code_seq.nextval,'FL00','FL12','보성군');
insert into code_master values(code_seq.nextval,'FL00','FL13','신안군');
insert into code_master values(code_seq.nextval,'FL00','FL14','영광군');
insert into code_master values(code_seq.nextval,'FL00','FL15','영암군');
insert into code_master values(code_seq.nextval,'FL00','FL16','완도군');
insert into code_master values(code_seq.nextval,'FL00','FL17','장성군');
insert into code_master values(code_seq.nextval,'FL00','FL18','장흥군');
insert into code_master values(code_seq.nextval,'FL00','FL19','진도군');
insert into code_master values(code_seq.nextval,'FL00','FL20','함평군');
insert into code_master values(code_seq.nextval,'FL00','FL21','해남군');
insert into code_master values(code_seq.nextval,'FL00','FL22','화순군');

insert into code_master values(code_seq.nextval,'FM00','FM01','군산시');--전라북도
insert into code_master values(code_seq.nextval,'FM00','FM02','김제시');
insert into code_master values(code_seq.nextval,'FM00','FM03','남원시');
insert into code_master values(code_seq.nextval,'FM00','FM04','익산시');
insert into code_master values(code_seq.nextval,'FM00','FM05','전주시 덕진구');
insert into code_master values(code_seq.nextval,'FM00','FM06','전주시 완산구');
insert into code_master values(code_seq.nextval,'FM00','FM07','정읍시');
insert into code_master values(code_seq.nextval,'FM00','FM08','고창군');
insert into code_master values(code_seq.nextval,'FM00','FM09','무주군');
insert into code_master values(code_seq.nextval,'FM00','FM10','부안군');
insert into code_master values(code_seq.nextval,'FM00','FM11','순창군');
insert into code_master values(code_seq.nextval,'FM00','FM12','완주군');
insert into code_master values(code_seq.nextval,'FM00','FM13','임실군');
insert into code_master values(code_seq.nextval,'FM00','FM14','장수군');
insert into code_master values(code_seq.nextval,'FM00','FM15','진안군');


insert into code_master values(code_seq.nextval,'FN00','FN01','제주시');--제주도
insert into code_master values(code_seq.nextval,'FN00','FN02','서귀포시');

insert into code_master values(code_seq.nextval,'FO00','FO01','계룡시');--충청남도
insert into code_master values(code_seq.nextval,'FO00','FO02','공주시');
insert into code_master values(code_seq.nextval,'FO00','FO03','논산시');
insert into code_master values(code_seq.nextval,'FO00','FO04','보령시');
insert into code_master values(code_seq.nextval,'FO00','FO05','서산시');
insert into code_master values(code_seq.nextval,'FO00','FO06','아산시');
insert into code_master values(code_seq.nextval,'FO00','FO07','천안시 동남구');
insert into code_master values(code_seq.nextval,'FO00','FO08','천안시 서북구');
insert into code_master values(code_seq.nextval,'FO00','FO09','금산군');
insert into code_master values(code_seq.nextval,'FO00','FO10','당진군');
insert into code_master values(code_seq.nextval,'FO00','FO11','부여군');
insert into code_master values(code_seq.nextval,'FO00','FO12','서천군');
insert into code_master values(code_seq.nextval,'FO00','FO13','연기군');
insert into code_master values(code_seq.nextval,'FO00','FO14','예산군');
insert into code_master values(code_seq.nextval,'FO00','FO15','청양군');
insert into code_master values(code_seq.nextval,'FO00','FO16','태안군');
insert into code_master values(code_seq.nextval,'FO00','FO17','홍성군');

insert into code_master values(code_seq.nextval,'FP00','FP01','제천시');--충청북도
insert into code_master values(code_seq.nextval,'FP00','FP02','청주시 상당구');
insert into code_master values(code_seq.nextval,'FP00','FP03','청주시 흥덕구');
insert into code_master values(code_seq.nextval,'FP00','FP04','충주시');
insert into code_master values(code_seq.nextval,'FP00','FP05','괴산군');
insert into code_master values(code_seq.nextval,'FP00','FP06','단양군');
insert into code_master values(code_seq.nextval,'FP00','FP07','보은군');
insert into code_master values(code_seq.nextval,'FP00','FP08','영동군');
insert into code_master values(code_seq.nextval,'FP00','FP09','옥천군');
insert into code_master values(code_seq.nextval,'FP00','FP10','음성군');
insert into code_master values(code_seq.nextval,'FP00','FP11','증평군');
insert into code_master values(code_seq.nextval,'FP00','FP12','진천군');
insert into code_master values(code_seq.nextval,'FP00','FP13','청원군');
=======
 
insert into code_master(code_no,code_type,code_code,code_name) values(code_seq.nextval,'0000','F000','과외희망지역');

=======
>>>>>>> .r89
insert into code_master values(code_seq.nextval,'F000','FA00','서울특별시');--시군구선택
insert into code_master values(code_seq.nextval,'F000','FB00','인천광역시');
insert into code_master values(code_seq.nextval,'F000','FC00','경기도');
insert into code_master values(code_seq.nextval,'F000','FD00','부산광역시');
insert into code_master values(code_seq.nextval,'F000','FE00','대구광역시');
insert into code_master values(code_seq.nextval,'F000','FF00','광주광역시');
insert into code_master values(code_seq.nextval,'F000','FG00','대전광역시');
insert into code_master values(code_seq.nextval,'F000','FH00','울산광역시');
insert into code_master values(code_seq.nextval,'F000','FI00','강원도');
insert into code_master values(code_seq.nextval,'F000','FJ00','경상남도');
insert into code_master values(code_seq.nextval,'F000','FK00','경상북도');
insert into code_master values(code_seq.nextval,'F000','FL00','전라남도');
insert into code_master values(code_seq.nextval,'F000','FM00','전라북도');
insert into code_master values(code_seq.nextval,'F000','FN00','제주도');
insert into code_master values(code_seq.nextval,'F000','FO00','충청남도');
insert into code_master values(code_seq.nextval,'F000','FP00','충청북도');



insert into code_master values(code_seq.nextval,'FA00','FA01','강남구');	--서울특별시
insert into code_master values(code_seq.nextval,'FA00','FA02','강동구');
insert into code_master values(code_seq.nextval,'FA00','FA03','강북구');
insert into code_master values(code_seq.nextval,'FA00','FA04','강서구');
insert into code_master values(code_seq.nextval,'FA00','FA05','관악구');
insert into code_master values(code_seq.nextval,'FA00','FA06','광진구');
insert into code_master values(code_seq.nextval,'FA00','FA07','구로구');
insert into code_master values(code_seq.nextval,'FA00','FA08','금천구');
insert into code_master values(code_seq.nextval,'FA00','FA09','노원구');
insert into code_master values(code_seq.nextval,'FA00','FA10','도봉구');
insert into code_master values(code_seq.nextval,'FA00','FA11','동대문구');
insert into code_master values(code_seq.nextval,'FA00','FA12','동작구');
insert into code_master values(code_seq.nextval,'FA00','FA13','마포구');
insert into code_master values(code_seq.nextval,'FA00','FA14','서대문구');
insert into code_master values(code_seq.nextval,'FA00','FA15','서초구');
insert into code_master values(code_seq.nextval,'FA00','FA16','성동구');
insert into code_master values(code_seq.nextval,'FA00','FA17','성북구');
insert into code_master values(code_seq.nextval,'FA00','FA18','송파구');
insert into code_master values(code_seq.nextval,'FA00','FA19','양천구');
insert into code_master values(code_seq.nextval,'FA00','FA20','영등포구');
insert into code_master values(code_seq.nextval,'FA00','FA21','용산구');
insert into code_master values(code_seq.nextval,'FA00','FA22','은평구');
insert into code_master values(code_seq.nextval,'FA00','FA23','종로구');
insert into code_master values(code_seq.nextval,'FA00','FA24','중구');
insert into code_master values(code_seq.nextval,'FA00','FA25','중랑구');

insert into code_master values(code_seq.nextval,'FB00','FB01','강화군');	--인천광역시
insert into code_master values(code_seq.nextval,'FB00','FB02','계양구');
insert into code_master values(code_seq.nextval,'FB00','FB03','남구');
insert into code_master values(code_seq.nextval,'FB00','FB04','남동구');
insert into code_master values(code_seq.nextval,'FB00','FB05','동구');
insert into code_master values(code_seq.nextval,'FB00','FB06','부평구');
insert into code_master values(code_seq.nextval,'FB00','FB07','서구');
insert into code_master values(code_seq.nextval,'FB00','FB08','연수구');
insert into code_master values(code_seq.nextval,'FB00','FB09','웅진군');
insert into code_master values(code_seq.nextval,'FB00','FB10','중구');

insert into code_master values(code_seq.nextval,'FC00','FC01','가평군');	--경기도
insert into code_master values(code_seq.nextval,'FC00','FC02','고양시');
insert into code_master values(code_seq.nextval,'FC00','FC03','고양 덕양구');
insert into code_master values(code_seq.nextval,'FC00','FC04','고양 일산동구');
insert into code_master values(code_seq.nextval,'FC00','FC05','고양 일산서구');
insert into code_master values(code_seq.nextval,'FC00','FC06','과천');
insert into code_master values(code_seq.nextval,'FC00','FC07','광명');
insert into code_master values(code_seq.nextval,'FC00','FC08','광주');
insert into code_master values(code_seq.nextval,'FC00','FC09','구리');
insert into code_master values(code_seq.nextval,'FC00','FC10','군포');
insert into code_master values(code_seq.nextval,'FC00','FC11','김포');
insert into code_master values(code_seq.nextval,'FC00','FC12','남양주');
insert into code_master values(code_seq.nextval,'FC00','FC13','동두천');
insert into code_master values(code_seq.nextval,'FC00','FC14','부천');
insert into code_master values(code_seq.nextval,'FC00','FC15','부천 소사구');
insert into code_master values(code_seq.nextval,'FC00','FC16','부천 오정구');
insert into code_master values(code_seq.nextval,'FC00','FC17','부천 원미구');
insert into code_master values(code_seq.nextval,'FC00','FC18','성남');
insert into code_master values(code_seq.nextval,'FC00','FC19','성남 분당구');
insert into code_master values(code_seq.nextval,'FC00','FC20','성남 수정구');
insert into code_master values(code_seq.nextval,'FC00','FC21','성남 중원구');
insert into code_master values(code_seq.nextval,'FC00','FC22','수원');
insert into code_master values(code_seq.nextval,'FC00','FC23','수원 권선구');
insert into code_master values(code_seq.nextval,'FC00','FC24','수원 영통구');
insert into code_master values(code_seq.nextval,'FC00','FC25','수원 장안구');
insert into code_master values(code_seq.nextval,'FC00','FC26','수원 팔달구');
insert into code_master values(code_seq.nextval,'FC00','FC27','시흥');
insert into code_master values(code_seq.nextval,'FC00','FC28','안산');
insert into code_master values(code_seq.nextval,'FC00','FC29','안산 단원구');
insert into code_master values(code_seq.nextval,'FC00','FC30','안산 상록구');
insert into code_master values(code_seq.nextval,'FC00','FC31','안성');
insert into code_master values(code_seq.nextval,'FC00','FC32','안양');
insert into code_master values(code_seq.nextval,'FC00','FC33','안양 동안구');
insert into code_master values(code_seq.nextval,'FC00','FC34','안양 만안구');
insert into code_master values(code_seq.nextval,'FC00','FC35','양주');
insert into code_master values(code_seq.nextval,'FC00','FC36','양평군');
insert into code_master values(code_seq.nextval,'FC00','FC37','여주군');
insert into code_master values(code_seq.nextval,'FC00','FC38','연천군');
insert into code_master values(code_seq.nextval,'FC00','FC39','오산');
insert into code_master values(code_seq.nextval,'FC00','FC40','용인');
insert into code_master values(code_seq.nextval,'FC00','FC41','용인 기흥구');
insert into code_master values(code_seq.nextval,'FC00','FC42','용인 수지구');
insert into code_master values(code_seq.nextval,'FC00','FC43','용인 처인구');
insert into code_master values(code_seq.nextval,'FC00','FC44','의왕');
insert into code_master values(code_seq.nextval,'FC00','FC45','의정부');
insert into code_master values(code_seq.nextval,'FC00','FC46','이천');
insert into code_master values(code_seq.nextval,'FC00','FC47','파주');
insert into code_master values(code_seq.nextval,'FC00','FC48','평택');
insert into code_master values(code_seq.nextval,'FC00','FC49','포천');
insert into code_master values(code_seq.nextval,'FC00','FC50','하남');
insert into code_master values(code_seq.nextval,'FC00','FC51','화성');

insert into code_master values(code_seq.nextval,'FD00','FD01','강서구');--부산광역시
insert into code_master values(code_seq.nextval,'FD00','FD02','금정구');
insert into code_master values(code_seq.nextval,'FD00','FD03','남구');
insert into code_master values(code_seq.nextval,'FD00','FD04','동구');
insert into code_master values(code_seq.nextval,'FD00','FD05','동래구');
insert into code_master values(code_seq.nextval,'FD00','FD06','부산진구');
insert into code_master values(code_seq.nextval,'FD00','FD07','북구');
insert into code_master values(code_seq.nextval,'FD00','FD08','사상구');
insert into code_master values(code_seq.nextval,'FD00','FD09','사하구');
insert into code_master values(code_seq.nextval,'FD00','FD10','서구');
insert into code_master values(code_seq.nextval,'FD00','FD11','수영구');
insert into code_master values(code_seq.nextval,'FD00','FD12','연제구');
insert into code_master values(code_seq.nextval,'FD00','FD13','영도구');
insert into code_master values(code_seq.nextval,'FD00','FD14','중구구');
insert into code_master values(code_seq.nextval,'FD00','FD15','해운대구');
insert into code_master values(code_seq.nextval,'FD00','FD16','기장군');

insert into code_master values(code_seq.nextval,'FE00','FE01','남구');--대구광역시
insert into code_master values(code_seq.nextval,'FE00','FE02','달서구');
insert into code_master values(code_seq.nextval,'FE00','FE03','동구');
insert into code_master values(code_seq.nextval,'FE00','FE04','북구');
insert into code_master values(code_seq.nextval,'FE00','FE05','서구');
insert into code_master values(code_seq.nextval,'FE00','FE06','수성구');
insert into code_master values(code_seq.nextval,'FE00','FE07','중구');
insert into code_master values(code_seq.nextval,'FE00','FE08','달성군');

insert into code_master values(code_seq.nextval,'FF00','FF01','광산구');--광주광역시
insert into code_master values(code_seq.nextval,'FF00','FF02','남구');
insert into code_master values(code_seq.nextval,'FF00','FF03','동구');
insert into code_master values(code_seq.nextval,'FF00','FF04','서구');


insert into code_master values(code_seq.nextval,'FG00','FG01','대덕구');--대전광역시
insert into code_master values(code_seq.nextval,'FG00','FG02','동구');
insert into code_master values(code_seq.nextval,'FG00','FG03','서구');
insert into code_master values(code_seq.nextval,'FG00','FG04','유성구');
insert into code_master values(code_seq.nextval,'FG00','FG05','중구');

insert into code_master values(code_seq.nextval,'FH00','FH01','남구');--울산광역시
insert into code_master values(code_seq.nextval,'FH00','FH02','동구');
insert into code_master values(code_seq.nextval,'FH00','FH03','북구');
insert into code_master values(code_seq.nextval,'FH00','FH04','중구');
insert into code_master values(code_seq.nextval,'FH00','FH05','울주군');

insert into code_master values(code_seq.nextval,'FI00','FI01','강릉시');--강원도
insert into code_master values(code_seq.nextval,'FI00','FI02','동해시');
insert into code_master values(code_seq.nextval,'FI00','FI03','삼척시');
insert into code_master values(code_seq.nextval,'FI00','FI04','속초시');
insert into code_master values(code_seq.nextval,'FI00','FI05','원주시');
insert into code_master values(code_seq.nextval,'FI00','FI06','춘천시');
insert into code_master values(code_seq.nextval,'FI00','FI07','태백시');
insert into code_master values(code_seq.nextval,'FI00','FI08','고성군');
insert into code_master values(code_seq.nextval,'FI00','FI09','양구군');
insert into code_master values(code_seq.nextval,'FI00','FI10','양양군');
insert into code_master values(code_seq.nextval,'FI00','FI11','영월군');
insert into code_master values(code_seq.nextval,'FI00','FI12','인제군');
insert into code_master values(code_seq.nextval,'FI00','FI13','정선군');
insert into code_master values(code_seq.nextval,'FI00','FI14','철원군');
insert into code_master values(code_seq.nextval,'FI00','FI15','평창군');
insert into code_master values(code_seq.nextval,'FI00','FI16','홍천군');
insert into code_master values(code_seq.nextval,'FI00','FI17','화천군');
insert into code_master values(code_seq.nextval,'FI00','FI18','횡성군');

insert into code_master values(code_seq.nextval,'FJ00','FJ01','거제시');--경상남도
insert into code_master values(code_seq.nextval,'FJ00','FJ02','김해시');
insert into code_master values(code_seq.nextval,'FJ00','FJ03','마산시');
insert into code_master values(code_seq.nextval,'FJ00','FJ04','밀양시');
insert into code_master values(code_seq.nextval,'FJ00','FJ05','사천시');
insert into code_master values(code_seq.nextval,'FJ00','FJ06','양산시');
insert into code_master values(code_seq.nextval,'FJ00','FJ07','진주시');
insert into code_master values(code_seq.nextval,'FJ00','FJ08','진해시');
insert into code_master values(code_seq.nextval,'FJ00','FJ09','창원시');
insert into code_master values(code_seq.nextval,'FJ00','FJ10','통영시');
insert into code_master values(code_seq.nextval,'FJ00','FJ11','거창군');
insert into code_master values(code_seq.nextval,'FJ00','FJ12','고성군');
insert into code_master values(code_seq.nextval,'FJ00','FJ13','남해군');
insert into code_master values(code_seq.nextval,'FJ00','FJ14','산청군');
insert into code_master values(code_seq.nextval,'FJ00','FJ15','의령군');
insert into code_master values(code_seq.nextval,'FJ00','FJ16','창녕군');
insert into code_master values(code_seq.nextval,'FJ00','FJ17','하동군');
insert into code_master values(code_seq.nextval,'FJ00','FJ18','함안군');
insert into code_master values(code_seq.nextval,'FJ00','FJ19','함양군');
insert into code_master values(code_seq.nextval,'FJ00','FJ20','합천군');

insert into code_master values(code_seq.nextval,'FK00','FK01','경산시');--경상북도
insert into code_master values(code_seq.nextval,'FK00','FK02','경주시');
insert into code_master values(code_seq.nextval,'FK00','FK03','구미시');
insert into code_master values(code_seq.nextval,'FK00','FK04','김천시');
insert into code_master values(code_seq.nextval,'FK00','FK05','문경시');
insert into code_master values(code_seq.nextval,'FK00','FK06','상주시');
insert into code_master values(code_seq.nextval,'FK00','FK07','안동시');
insert into code_master values(code_seq.nextval,'FK00','FK08','영주시');
insert into code_master values(code_seq.nextval,'FK00','FK09','영천시');
insert into code_master values(code_seq.nextval,'FK00','FK10','포항시 남구');
insert into code_master values(code_seq.nextval,'FK00','FK11','포항시 북구');
insert into code_master values(code_seq.nextval,'FK00','FK12','고령군');
insert into code_master values(code_seq.nextval,'FK00','FK13','군위군');
insert into code_master values(code_seq.nextval,'FK00','FK14','봉화군');
insert into code_master values(code_seq.nextval,'FK00','FK15','성주군');
insert into code_master values(code_seq.nextval,'FK00','FK16','영덕군');
insert into code_master values(code_seq.nextval,'FK00','FK17','영양군');
insert into code_master values(code_seq.nextval,'FK00','FK18','예천군');
insert into code_master values(code_seq.nextval,'FK00','FK19','울릉군');
insert into code_master values(code_seq.nextval,'FK00','FK20','울진군');
insert into code_master values(code_seq.nextval,'FK00','FK21','의성군');
insert into code_master values(code_seq.nextval,'FK00','FK22','청도군');
insert into code_master values(code_seq.nextval,'FK00','FK23','청송군');
insert into code_master values(code_seq.nextval,'FK00','FK24','칠곡군');

insert into code_master values(code_seq.nextval,'FL00','FL01','광양시');--전라남도
insert into code_master values(code_seq.nextval,'FL00','FL02','나주시');
insert into code_master values(code_seq.nextval,'FL00','FL03','목포시');
insert into code_master values(code_seq.nextval,'FL00','FL04','순천시');
insert into code_master values(code_seq.nextval,'FL00','FL05','여수시');
insert into code_master values(code_seq.nextval,'FL00','FL06','강진군');
insert into code_master values(code_seq.nextval,'FL00','FL07','고흥군');
insert into code_master values(code_seq.nextval,'FL00','FL08','곡성군');
insert into code_master values(code_seq.nextval,'FL00','FL09','구례군');
insert into code_master values(code_seq.nextval,'FL00','FL10','담양군');
insert into code_master values(code_seq.nextval,'FL00','FL11','무안군');
insert into code_master values(code_seq.nextval,'FL00','FL12','보성군');
insert into code_master values(code_seq.nextval,'FL00','FL13','신안군');
insert into code_master values(code_seq.nextval,'FL00','FL14','영광군');
insert into code_master values(code_seq.nextval,'FL00','FL15','영암군');
insert into code_master values(code_seq.nextval,'FL00','FL16','완도군');
insert into code_master values(code_seq.nextval,'FL00','FL17','장성군');
insert into code_master values(code_seq.nextval,'FL00','FL18','장흥군');
insert into code_master values(code_seq.nextval,'FL00','FL19','진도군');
insert into code_master values(code_seq.nextval,'FL00','FL20','함평군');
insert into code_master values(code_seq.nextval,'FL00','FL21','해남군');
insert into code_master values(code_seq.nextval,'FL00','FL22','화순군');

insert into code_master values(code_seq.nextval,'FM00','FM01','군산시');--전라북도
insert into code_master values(code_seq.nextval,'FM00','FM02','김제시');
insert into code_master values(code_seq.nextval,'FM00','FM03','남원시');
insert into code_master values(code_seq.nextval,'FM00','FM04','익산시');
insert into code_master values(code_seq.nextval,'FM00','FM05','전주시 덕진구');
insert into code_master values(code_seq.nextval,'FM00','FM06','전주시 완산구');
insert into code_master values(code_seq.nextval,'FM00','FM07','정읍시');
insert into code_master values(code_seq.nextval,'FM00','FM08','고창군');
insert into code_master values(code_seq.nextval,'FM00','FM09','무주군');
insert into code_master values(code_seq.nextval,'FM00','FM10','부안군');
insert into code_master values(code_seq.nextval,'FM00','FM11','순창군');
insert into code_master values(code_seq.nextval,'FM00','FM12','완주군');
insert into code_master values(code_seq.nextval,'FM00','FM13','임실군');
insert into code_master values(code_seq.nextval,'FM00','FM14','장수군');
insert into code_master values(code_seq.nextval,'FM00','FM15','진안군');


insert into code_master values(code_seq.nextval,'FN00','FN01','제주시');--제주도
insert into code_master values(code_seq.nextval,'FN00','FN02','서귀포시');

insert into code_master values(code_seq.nextval,'FO00','FO01','계룡시');--충청남도
insert into code_master values(code_seq.nextval,'FO00','FO02','공주시');
insert into code_master values(code_seq.nextval,'FO00','FO03','논산시');
insert into code_master values(code_seq.nextval,'FO00','FO04','보령시');
insert into code_master values(code_seq.nextval,'FO00','FO05','서산시');
insert into code_master values(code_seq.nextval,'FO00','FO06','아산시');
insert into code_master values(code_seq.nextval,'FO00','FO07','천안시 동남구');
insert into code_master values(code_seq.nextval,'FO00','FO08','천안시 서북구');
insert into code_master values(code_seq.nextval,'FO00','FO09','금산군');
insert into code_master values(code_seq.nextval,'FO00','FO10','당진군');
insert into code_master values(code_seq.nextval,'FO00','FO11','부여군');
insert into code_master values(code_seq.nextval,'FO00','FO12','서천군');
insert into code_master values(code_seq.nextval,'FO00','FO13','연기군');
insert into code_master values(code_seq.nextval,'FO00','FO14','예산군');
insert into code_master values(code_seq.nextval,'FO00','FO15','청양군');
insert into code_master values(code_seq.nextval,'FO00','FO16','태안군');
insert into code_master values(code_seq.nextval,'FO00','FO17','홍성군');

insert into code_master values(code_seq.nextval,'FP00','FP01','제천시');--충청북도
insert into code_master values(code_seq.nextval,'FP00','FP02','청주시 상당구');
insert into code_master values(code_seq.nextval,'FP00','FP03','청주시 흥덕구');
insert into code_master values(code_seq.nextval,'FP00','FP04','충주시');
insert into code_master values(code_seq.nextval,'FP00','FP05','괴산군');
insert into code_master values(code_seq.nextval,'FP00','FP06','단양군');
insert into code_master values(code_seq.nextval,'FP00','FP07','보은군');
insert into code_master values(code_seq.nextval,'FP00','FP08','영동군');
insert into code_master values(code_seq.nextval,'FP00','FP09','옥천군');
insert into code_master values(code_seq.nextval,'FP00','FP10','음성군');
insert into code_master values(code_seq.nextval,'FP00','FP11','증평군');
insert into code_master values(code_seq.nextval,'FP00','FP12','진천군');
insert into code_master values(code_seq.nextval,'FP00','FP13','청원군');

insert into code_master(code_no,code_type,code_code,code_name) values(code_seq.nextval,'G000','G001','자신의 보물 제 1호는?');
insert into code_master(code_no,code_type,code_code,code_name) values(code_seq.nextval,'G000','G002','다시 태어나면 되고 싶은 것은?');
insert into code_master(code_no,code_type,code_code,code_name) values(code_seq.nextval,'G000','G003','가장 기억에 남는 선생님의 성함은?');
insert into code_master(code_no,code_type,code_code,code_name) values(code_seq.nextval,'G000','G004','초등학교 때 기억에 남는 짝꿍 이름은?');
insert into code_master(code_no,code_type,code_code,code_name) values(code_seq.nextval,'G000','G005','인생 깊게 읽은 책 이름은?');


--선생님 테이블

drop table wmt_teacher;

create table wmt_teacher(
 	teacherid varchar2(50) primary key, 
	name varchar2(50) not null,
	password varchar2(50) not null,
	gender varchar2(50) not null,--code
	tel varchar2(50),
	hoperegion varchar2(50) not null,--code
	email varchar2(50),
	questionPass varchar2(100),
	answerpass varchar2(100), 
	originalfilename varchar2(100),
	savefilename varchar2(100),
	enrolldate varchar2(50) not null,
	withdrawaldate varchar2(50),
	university varchar2(50), 
	hopesubject varchar2(50) not null, --code
	hopegender varchar2(50) not null, --code
	availablestudent varchar2(50) not null, --code
	availableday varchar2(50), -- 선택된 요일을 묶어서 저장 "월수"
	availabletime varchar2(50), -- 가능 시작 시간과 종료시간을 묶어서 저장 "0305"
	intro varchar2(1000),
	CONSTRAINT fk_teacher_gender
	foreign key(gender) references code_master(code_code),
	CONSTRAINT fk_teacher_hoperegion
	foreign key(hoperegion) references code_master(code_code),
	CONSTRAINT fk_teacher_hopesubject
	foreign key(hopesubject) references code_master(code_code),
	CONSTRAINT fk_teacher_hopegender
	foreign key(hopegender) references code_master(code_code),
	CONSTRAINT fk_teacher_availablestudent
	foreign key(availablestudent) references code_master(code_code)
 )

 alter table wmt_teacher modify withdrawaldate varchar2(50) null
 select * from wmt_teacher
 update wmt_teacher set withdrawaldate=null where id ='java'

 ALTER TABLE wmt_student DROP COLUMN GRADE

 --학생 테이블
  create table wmt_student(
   studentid varchar2(50) primary key,
   name varchar2(50) not null,
   password varchar2(50) not null,
   gender varchar2(50) not null, 
   tel varchar2(50),
   hoperegion varchar2(50) not null, 
   email varchar2(50),
   questionpass varchar2(100) not null,
   answerpass varchar2(100) not null,
   enrolldate varchar2(50) not null,
   withdrawaldate varchar2(50),
   hopesubject varchar2(50) not null,
   grade varchar2(50) not null,
   zipcode varchar2(10),
   codeaddress varchar2(200), 
   subaddress varchar2(150),
   CONSTRAINT fk_student_gender
	foreign key(gender) references code_master(code_code),
	CONSTRAINT fk_student_hoperegion
	foreign key(hoperegion) references code_master(code_code),
	CONSTRAINT fk_student_hopesubject
	foreign key(hopesubject) references code_master(code_code)
  )
   
  
  drop table wmt_student
  select * from wmt_student
   alter table wmt_student modify withdrawaldate varchar2(50) null
   alter table wmt_student modify hopesubject varchar2(50) not null

  --선생님,학생 리스트 인서트 sql
   insert into WMT_teacher values('java','아이유','1111','여','1234','서울','iu@naver.com','할아버지 성함?','아이유할배',
  'iu','new iu','0619','0620','중앙대','수학','여','중3','월','3시','안냥');
  insert into WMT_teacher values('java3','아이유','1111','여','1234','서울','iu@naver.com','할아버지 성함?','아이유할배',
  'iu','new iu','0619','0620','중앙대','수학','여','중3','월','3시','안냥');
  
  insert into WMT_teacher(teacherid,name,password,gender,
  			tel,hoperegion,email,questionpass,answerpass,
 			enrolldate,university,hopesubject,
 			hopegender,availablestudent,availableday,availabletime,intro) 
 			values('teacher1','수지','1111','A002',
 			'1234','FC19','suzi@naver.com','할아버지 성함?','아이유할배',
  			'0619','중앙대','C202',
  			'A002','D203','월수','0305','안냥');
  insert into WMT_teacher(teacherid,name,password,gender,
  			tel,hoperegion,email,questionpass,answerpass,
 			enrolldate,university,hopesubject,
 			hopegender,availablestudent,availableday,availabletime,intro) 
 			values('teacher2','수현','1111','A001',
 			'1234','FC20','suhyun@naver.com','할아버지 성함?','수현할배',
  			'0619','중앙대','C301',
  			'A002','D202','수','1205','안냥');
  insert into WMT_teacher(teacherid,name,password,gender,
  			tel,hoperegion,email,questionpass,answerpass,
 			enrolldate,university,hopesubject,
 			hopegender,availablestudent,availableday,availabletime,intro) 
 			values('teacher3','현우','1111','A001',
 			'1234','FC21','hyunwoo@naver.com','할아버지 성함?','현우할배',
  			'0619','중앙대','C101',
  			'A002','D201','수','0812','안냥');
  			
 insert into WMT_student(studentid,name,password,
   gender, tel,
   hoperegion,email,questionpass,answerpass,enrolldate,
   hopesubject,grade,zipcode,codeaddress,subaddress)
 			values('student1','아이유','1111',
 			'A002','1234',
 			'FC19','iu@naver.com','할아버지 성함?','아이유할배','0619',
 			'C202','D202','20','성남시 분당구', '703동 502호');
 insert into WMT_student(studentid,name,password,
   gender, tel,
   hoperegion,email,questionpass,answerpass,enrolldate,
   hopesubject,grade,zipcode,codeaddress,subaddress)
 			values('student2','연희','1111',
 			'A002','1234',
 			'FC20','yeonhee@naver.com','할아버지 성함?','연희할배','0619',
 			'C301','D203','21','성남시 수정구', '703동 503호');
 insert into WMT_student(studentid,name,password,
   gender, tel,
   hoperegion,email,questionpass,answerpass,enrolldate,
   hopesubject,grade,zipcode,codeaddress,subaddress)
 			values('student3','승기','1111',
 			'A001','1234',
 			'FC21','suzi@naver.com','할아버지 성함?','승기할배','0619',
 			'C101','D201','22','성남시 중원구', '703동 504호');

  


create table notice(
	no number primary key,
	title varchar2(200) not null,
	dateposted date not null,
	writer varchar2(50) not null,
	hits number default 0,
	orgfilename varchar2(200),
	savfilename varchar2(200),
	content varchar2(3000) not null
);
drop table notice;

create sequence notice_seq nocache;
drop sequence notice_seq;

insert into notice(no,title,dateposted,writer,content) values(notice_seq.nextval,'테스트',sysdate,'관리자','테스트용 입니다.');

select * from notice;

create table help(
	no number primary key,
	id varchar2(50) not null,
	title varchar2(200) not null,
	dateposted date not null,
	hits number default 0,
	content varchar2(3000) not null,
	ref number default 0,
	restep number default 0,
	relevel number default 0
);
drop table help;

create sequence help_seq nocache;
drop sequence help_seq;

insert into help(no,id,title,content,dateposted) 
values(help_seq.nextval,'admin','테스트','테스트입니다.',sysdate);


select * from help;

select * from addresscode where dong='나운2동';

  
 drop table wmt_teacher_lessonlist
  --선생님이 찜한 리스트
 create table wmt_teacher_lessonlist(
	no number primary key,
	teacherid varchar2(50) not null,
	lessonno number not null,
	selectedhit number default 0,
	CONSTRAINT fk_teacherlesson_teacher 
	foreign key(teacherid) references wmt_teacher(teacherid),
	CONSTRAINT fk_teacherlesson_lesson 
	foreign key(lessonno) references wmt_lesson(lessonno)
)
create sequence wmt_teacher_lessonlist_seq;--시퀀스 생성
drop sequence wmt_teacher_lessonlist_seq;
insert into wmt_teacher_lessonlist(no,teacherid,lessonno)
values(wmt_teacher_lessonlist_seq.nextval,'java','1')--예시

--학생이 선생이 찜한 리스트
create table wmt_student_teacherlist(
	no number primary key,
	studentid varchar2(50) not null,
	teacherid varchar2(50) not null,
	selectedhit number default 0,
	CONSTRAINT fk_studentteacher_student 
	foreign key(studentid) references wmt_student(studentid),
	CONSTRAINT fk_studentteacher_teacher 
	foreign key(teacherid) references wmt_teacher(teacherid)
)
drop table wmt_student_teacherlist
create sequence wmt_student_teacherlist_seq;--시퀀스 생성
drop sequence wmt_student_teacherlist_seq;

insert into wmt_student_teacherlist(no,studentid,teacherid)
values(wmt_student_teacherlist_seq.nextval,'java2','java') --예시

  select stl.*,s.* 
	    from wmt_student_teacherlist stl, wmt_student s 
	    where stl.teacherid='java' and stl.studentid=s.studentid
	 
drop table wmt_student_teacherlist

--수업등록 테이블
insert into wmt_lesson(lessonno,studentid,title,content,hits,dateposted,
hopeday,hopetime,classteacherid) 
values(lessonno_seq.nextval,'java2','수업등록 글쓰기 테스트',
'수학과외 구합니다.',0,sysdate,'월','3시','java')
alter table wmt_lesson modify(classteacherid varchar2(50) null);
select lessonno_seq.nextval from dual
CREATE TABLE wmt_lesson(
	lessonno number primary key,
	studentid varchar2(50) not null,
	title varchar2(100) not null,
	content varchar2(2000),
	hits number default 0,
	dateposted date not null,
	hopeday varchar2(100) not null,
	hopetime varchar2(100) not null,
	connectstate varchar2(50) default '연결가능',
	classteacherid varchar2(50),
	CONSTRAINT FK_lesson_student 
	foreign key(studentid) 
	references wmt_student(studentid),
	CONSTRAINT FK_lesson_classteacherid 
	foreign key(classteacherid) 
	references wmt_teacher(teacherid)
)
create sequence lessonno_seq;
drop sequence lessonno_seq;
drop table wmt_lesson
select *  from wmt_lesson

select lessonno,title,studentid,grade,hopesubject,dateposted,hits 
from (
 	select lessonno,title,studentid,grade,hopesubject,dateposted,hits,ceil(rownum/10) as page from (
 	select l.lessonno,l.title,l.studentid,s.name,c1.code_name as hopesubject,c2.code_name as grade, l.dateposted,l.hits 
 	from WMT_LESSON l,wmt_student s,CODE_MASTER c1, CODE_MASTER c2 
 	where  l.studentid=s.studentid and c1.code_code=s.hopesubject 
 	and c2.code_code=s.grade order by lessonno desc
 	) 
 	) where page='1'
 	
select s.studentid,s.name, c1.code_name as gender,c2.code_name as hopesubject, c3.code_name as grade 
from WMT_STUDENT s,CODE_MASTER c1, CODE_MASTER c2, CODE_MASTER c3 
where studentid='student1' 
and password='1111'
and c1.code_code=s.gender
and c2.code_code=s.hopesubject
and c3.code_code=s.grade
		
		select s.name, s.tel, l.*,c1.code_name as gender,c2.code_name as hopesubject, c3.code_name as grade
from WMT_STUDENT s,WMT_LESSON l,CODE_MASTER c1, CODE_MASTER c2, CODE_MASTER c3
where l.lessonno=#{lessonno}
and s.studentid=#{studentid}
and l.studentid=s.studentid 
and c1.code_code=s.gender
and c2.code_code=s.hopesubject
and c3.code_code=s.grade

select t.teacherid,
t.name, 
t.tel, 
c4.code_name as hoperegion,
t.email, 
t.questionpass, 
t.answerpass, 
t.enrolldate, 
t.withdrawaldate,
c1.code_name as gender,
c2.code_name as hopesubject, 
c3.code_name as grade,
t.university,
c5.code_name as hopegender,
c6.code_name as availablestudent,
t.availableday,t.availabletime,
t.intro
from WMT_TEACHER t,CODE_MASTER c1, CODE_MASTER c2, CODE_MASTER c3,
CODE_MASTER c4, CODE_MASTER c5, CODE_MASTER c6
where teacherid=#{id} 
and password=#{password}
and c1.code_code=t.gender
and c2.code_code=t.hopesubject
and c3.code_code=t.grade
and c4.code_code=t.hoperegion
and c5.code_code=t.hopegender
and c6.code_code=t.availablestudent